package com.yeyks.common.utils.excel;

import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.yeyks.common.exception.ServiceException;
import com.yeyks.common.utils.DateUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.nio.charset.StandardCharsets;
import java.util.List;
import java.util.Map;

/**
 * @author fuchangshuai
 * @date 2019/05/07 10:22
 */
public class ExcelUtil {
    /**
     * 读取 Excel(多个 sheet)
     *
     * @param excel    文件
     * @param rowModel 实体类映射，继承 BaseRowModel 类
     * @return Excel 数据 list
     */
    public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel) {
        ExcelListener excelListener = new ExcelListener();
        ExcelReader reader = getReader(excel, excelListener);
        if (reader == null) {
            return null;
        }
        for (Sheet sheet : reader.getSheets()) {
            if (rowModel != null) {
                sheet.setClazz(rowModel.getClass());
            }
            reader.read(sheet);
        }
        return excelListener.getDatas();
    }

    /**
     * 读取某个 sheet 的 Excel
     *
     * @param excel    文件
     * @param rowModel 实体类映射，继承 BaseRowModel 类
     * @param sheetNo  sheet 的序号 从1开始
     * @return Excel 数据 list
     */
    public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel, int sheetNo) {
        return readExcel(excel, rowModel, sheetNo, 1);
    }

    /**
     * 读取某个 sheet 的 Excel
     *
     * @param excel       文件
     * @param rowModel    实体类映射，继承 BaseRowModel 类
     * @param sheetNo     sheet 的序号 从1开始
     * @param headLineNum 表头行数，默认为1
     * @return Excel 数据 list
     */
    public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel, int sheetNo,
                                         int headLineNum) {
        ExcelListener excelListener = new ExcelListener();
        ExcelReader reader = getReader(excel, excelListener);
        if (reader == null) {
            return null;
        }
        reader.read(new Sheet(sheetNo, headLineNum, rowModel.getClass()));
        return excelListener.getDatas();
    }

    /**
     * 导出 Excel ：一个 sheet，带表头
     *
     * @param response HttpServletResponse
     * @param list     数据 list，每个元素为一个 BaseRowModel
     * @param fileName 导出的文件名
     * @param object   映射实体类，Excel 模型
     */
    public static void writeExcel(HttpServletResponse response, List<? extends BaseRowModel> list,
                                  String fileName, BaseRowModel object) {
        String sheetName = "sheet1";
        Map<String, Object> map = getFileAndStream(fileName, response);
        File file = (File) map.get("file");
        ServletOutputStream outputStream = (ServletOutputStream) map.get("outputStream");
        ExcelWriter writer = new ExcelWriter(null, outputStream, ExcelTypeEnum.XLSX, true, new StyleExcelHandler());
        Sheet sheet = new Sheet(1, 0, object.getClass());
        sheet.setAutoWidth(true);
        sheet.setSheetName(sheetName);
        writer.write(list, sheet);
        writer.finish();
        file.delete();
    }

    /**
     * 导出 Excel ：多个 sheet，带表头
     *
     * @param response  HttpServletResponse
     * @param list      数据 list，每个元素为一个 BaseRowModel
     * @param fileName  导出的文件名
     * @param sheetName 导入文件的 sheet 名
     * @param object    映射实体类，Excel 模型
     */
    public static ExcelWriterFactory writeExcelWithSheets(HttpServletResponse response, List<? extends BaseRowModel> list,
                                                          String fileName, String sheetName, BaseRowModel object) {
        Map<String, Object> map = getFileAndStream(fileName, response);
        ServletOutputStream outputStream = (ServletOutputStream) map.get("outputStream");
        ExcelWriterFactory writer = new ExcelWriterFactory(outputStream, ExcelTypeEnum.XLSX);
        Sheet sheet = new Sheet(1, 0, object.getClass());
        sheet.setSheetName(sheetName);
        writer.write(list, sheet);
        return writer;
    }

    /**
     * 导出 Excel ：多个 sheet，带表头
     *
     * @param response HttpServletResponse
     * @param list     数据 list，每个元素为一个 BaseRowModel
     * @param object   映射实体类，Excel 模型
     */
    public static void disposeWriteExcelWithSheets(HttpServletResponse response, List<? extends BaseRowModel> list, BaseRowModel object) {
        if (null == list) {
            list = Lists.newArrayList();
        }
        List<? extends List<? extends BaseRowModel>> lists = Lists.partition(list, 10000);
        ExcelWriterFactory writerFactory = null;
        for (int i = 0; i < lists.size(); i++) {
            if (null == writerFactory) {
                writerFactory = ExcelUtil.writeExcelWithSheets(response, lists.get(0), DateUtils.nowToYMDHMSStr(), "sheet" + i, object);
            } else {
                writerFactory.write(lists.get(i), "sheet" + i, object);
            }
        }
        if (null != writerFactory) {
            writerFactory.finish();
        }
    }

    /**
     * 导出文件时为Writer生成outputStream和file
     */
    private static Map<String, Object> getFileAndStream(String fileName, HttpServletResponse response) {
        //创建本地文件
        String filePath = fileName + ".xlsx";
        File dbfFile = new File(filePath);
        try {
            if (!dbfFile.exists() || dbfFile.isDirectory()) {
                dbfFile.createNewFile();
            }
            fileName = new String(filePath.getBytes(), StandardCharsets.ISO_8859_1);
            response.addHeader("Content-Disposition", "filename=" + fileName);
            Map<String, Object> map = Maps.newHashMap();
            map.put("file", dbfFile);
            map.put("outputStream", response.getOutputStream());
            return map;
        } catch (IOException e) {
            throw new ServiceException("创建文件失败！");
        }
    }

    /**
     * 返回 ExcelReader
     *
     * @param excel         需要解析的 Excel 文件
     * @param excelListener new ExcelListener()
     */
    private static ExcelReader getReader(MultipartFile excel, ExcelListener excelListener) {
        String filename = excel.getOriginalFilename();
        if (filename == null || (!filename.toLowerCase().endsWith(".xls") && !filename.toLowerCase().endsWith(".xlsx"))) {
            throw new ServiceException("文件格式错误！");
        }
        InputStream inputStream;
        try {
            inputStream = new BufferedInputStream(excel.getInputStream());
            return new ExcelReader(inputStream, null, excelListener, false);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }
}
